package BD;

import java.lang.reflect.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import ClasesBase.Fila;

public class BDDeclaration implements BDInterface {

    private Connection connection;
    

	public ArrayList<Fila> obtenerFilasMaquina(int maquina) throws Exception{
		ArrayList<Fila> filas = new ArrayList();
		String sql = "SELECT * FROM Fila WHERE idmaquina = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, maquina);
		ResultSet rs = preparedStatement.executeQuery();
		while (rs.next()){
			Fila f = new Fila();
			f.setPos(rs.getInt(1));
			f.setId_producto(rs.getInt(3));
			f.setCantidad(rs.getInt(4));
			f.setCantidadMaxima(rs.getInt(5));
			f.setFechaUltimaRecarga(rs.getString(6));
			filas.add(f);
		}
		preparedStatement.close();		
		return filas;
	}
    
	public String comprobarUltimaRecarga(int maquina, int fila) throws Exception{
		String fecha = null;
		String sql = "SELECT fechaultimarecarga FROM FILA WHERE id = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, fila);
		ResultSet rs = preparedStatement.executeQuery();
		fecha = rs.getString(1);
		preparedStatement.close();
		return fecha;
	}

	public int comprobarCantidadFila(int maquina, int fila) throws Exception{
		int cant = 0;
		String sql = "SELECT cantidad FROM Fila WHERE id = ? AND idmaquina = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, fila);
		preparedStatement.setInt(2, maquina);
		ResultSet rs = preparedStatement.executeQuery();
		cant = rs.getInt(1);
		preparedStatement.close();
		return cant;
	}
	
	public void init() throws Exception {
		try {
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager.getConnection("jdbc:sqlite:lib/BDVending.s3db");
		} catch(Exception ex) {
			JOptionPane.showMessageDialog(null, ex.getMessage(), ex.getClass().getName(),
					JOptionPane.ERROR_MESSAGE);
		}
	}


	public void end() throws Exception {
		connection.close();
	}

	public void cambiarProductoFila(int maquina, int fila, int id_producto) throws Exception {
		String sql = "UPDATE Fila SET idproducto = ? where id = ? AND idmaquina = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, id_producto);
		preparedStatement.setInt(2, fila);
		preparedStatement.setInt(3, maquina);
		preparedStatement.executeUpdate();
		preparedStatement.close();
		
	}

	public void vaciarFila(int maquina, int fila) throws Exception {
		String sql = "UPDATE Fila SET cantidad = '0' where id = ? AND idmaquina = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, fila);
		preparedStatement.setInt(2, maquina);
		preparedStatement.executeUpdate();
		preparedStatement.close();
		
	}

	public void cambiarCantidad(int maquina, int fila, int cantidad) throws Exception {
		String sql = "UPDATE Fila SET cantidad = ? WHERE id = ? AND idmaquina = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, cantidad);
		preparedStatement.setInt(2, fila);
		preparedStatement.setInt(3, maquina);
		preparedStatement.executeUpdate();	
		preparedStatement.close();
	}

	public double comprobarDineroTotal(int maquina) throws Exception {
		double money = 0;
		
		String sql = "SELECT total FROM Maquina WHERE id = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, maquina);
		ResultSet rs = preparedStatement.executeQuery();
		money = rs.getDouble(1);
		preparedStatement.close();
		return money;
	}

	public String comprobarMonedas(int maquina) throws Exception {
		String monedas = null;
		int cantidad;
		
		String sql = "SELECT cant5cent FROM Maquina where id = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, maquina);
		ResultSet rs = preparedStatement.executeQuery();
		cantidad = rs.getInt(1);
		monedas = cantidad+ " monedas de 5 cent";
		
		String sql2 = "SELECT cant10cent FROM Maquina where id = ?";
		PreparedStatement preparedStatement2 = connection.prepareStatement(sql2);
		preparedStatement2.setInt(1, maquina);
		ResultSet rs2 = preparedStatement2.executeQuery();
		cantidad = rs2.getInt(1);
		monedas = monedas+ ", "  +cantidad+ " monedas de 10 cent";
		
		String sql3 = "SELECT cant20cent FROM Maquina where id = ?";
		PreparedStatement preparedStatement3 = connection.prepareStatement(sql3);
		preparedStatement3.setInt(1, maquina);
		ResultSet rs3 = preparedStatement3.executeQuery();
		cantidad = rs3.getInt(1);
		monedas = monedas+ ", "  +cantidad+ " monedas de 25 cent";
		
		String sql4 = "SELECT cant50cent FROM Maquina where id = ?";
		PreparedStatement preparedStatement4 = connection.prepareStatement(sql4);
		preparedStatement4.setInt(1, maquina);
		ResultSet rs4 = preparedStatement4.executeQuery();
		cantidad = rs4.getInt(1);
		monedas = monedas+ ", "  +cantidad+ " monedas de 50 cent";
		
		String sql5 = "SELECT cant1e FROM Maquina where id = ?";
		PreparedStatement preparedStatement5 = connection.prepareStatement(sql5);
		preparedStatement5.setInt(1, maquina);
		ResultSet rs5 = preparedStatement5.executeQuery();
		cantidad = rs5.getInt(1);
		monedas = monedas+ ", "  +cantidad+ " monedas de 1 euro";
		
		String sql6 = "SELECT cant2e FROM Maquina where id = ?";
		PreparedStatement preparedStatement6 = connection.prepareStatement(sql6);
		preparedStatement6.setInt(1, maquina);
		ResultSet rs6 = preparedStatement6.executeQuery();
		cantidad = rs6.getInt(1);
		monedas = monedas+ ", "  +cantidad+ " monedas de 2 euros";
		
		preparedStatement.close();
		return monedas;
	}

	public void cambiarFechaUltimaRecarga(int maquina, int fila, String fecha) throws Exception{
		String sql = "UPDATE Fila SET fechaultimarecarga = ? WHERE idmaquina = ? AND id = ?";
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.setString(1, fecha);
		ps.setInt(2, maquina);
		ps.setInt(3, fila);
		ps.executeUpdate();
	}
	
	public ArrayList<Integer> obtenerIDMaquinas() throws Exception{
		ArrayList<Integer> ids = new ArrayList<Integer>();
		String sql = "SELECT id FROM Maquina";
		PreparedStatement ps = connection.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()){
			ids.add(rs.getInt(1));
		}
		
		return ids;
	}

	@Override
	public void modificarCantidadProducto(int maquina, int fila, int cantidad)
			throws Exception {
		String sql = "UPDATE Fila SET cantidad = ? WHERE id_maquina = ? AND id = ?";
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.setInt(1, cantidad);
		ps.setInt(2, maquina);
		ps.setInt(3, fila);
		ps.executeUpdate();
		
	}

	@Override
	public void actualizarMonedas(int maquina, int cinco, int diez, int veinte,
			int cincuenta, int uno, int dos) throws Exception {
		String sql = "UPDATE Maquina SET cant5cent = ?, cant10cent = ?, cant20cent = ?," +
				" cant50cent = ?, cant1e = ?, cant2e = ? WHERE id = ?";
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.setInt(1, cinco);
		ps.setInt(2, diez);
		ps.setInt(3, veinte);
		ps.setInt(4, cincuenta);
		ps.setInt(5, uno);
		ps.setInt(6, dos);
		ps.setInt(7, maquina);
		ps.executeUpdate();
		
		
	}

	public void actualizarFilasMaquina(ArrayList<Fila> filas, int maquina) throws Exception{
		
			for (int i=0; i<filas.size(); i++){
				String sql = "UPDATE Fila SET idproducto = ?, cantidad = ?, cantidadMax = ?," +
						" fechaUltimaRecarga = ? WHERE id = ? AND idmaquina = ?";
				PreparedStatement ps = connection.prepareStatement(sql);
				ps.setInt(1, filas.get(i).getId_producto());
				ps.setInt(2, filas.get(i).getCantidad());
				ps.setInt(3, filas.get(i).getCantidadMaxima());
				ps.setString(4, filas.get(i).getFechaUltimaRecarga());
				ps.setInt(5, filas.get(i).getPos());
				ps.setInt(6, maquina);
				ps.executeUpdate();
			}
		
	}
	
}
